<?php

/**
 * Created by PhpStorm.
 * User: xuhon
 * Date: 2016/9/22
 * Time: 14:18
 */
require_once (FRAMEWORK_PATH . "/class/db/ez_results.smarty.php");
class DB
{
    /**
     * DB实例对象
     * @var null
     */
    private static $DB = null;
    /**
     * 数据库操作对象
     * @var ezSQL_mysql|PDOX
     */
    public $db;
    /**
     * 分页数据库操作对象
     * @var smart_ez_results
     */
    public $ezr; //分页
    /**
     * 表名
     * @var
     */
    private $table; //表名
    /**
     * 表别名
     * @var string
     */
    private $alias = '';
    /**
     * 单条记录对象
     * @var
     */
    private $data;//单条记录
    /**
     * 原始记录
     * @var
     */
    private $origin_data;
    /**
     * 是否打印log，true为打印，false为不打印
     * @var bool
     */
    private $printLog = false;//是否打印sql，并终止
    /**
     * 查询条件
     * @var string
     */
    private $where = '1 ';
    /**
     * 查询绑定条件
     * @var array
     */
    private $where_param = array();
    /**
     * 排序条件
     * @var string
     */
    private $sort = '';
    /**
     * 查询条数
     * @var int
     */
    private $limit = 0;
    /**
     * 连接的表
     * @var array
     */
    private $join = array();
    /**
     * 连接的表别名
     * @var array
     */
    private $join_alias = array();
    /**
     * 连接对象
     * @var array
     */
    private $join_type = array();
    /**
     * 联表条件
     * @var array
     */
    private $on = array();
    /**
     * 最新加入的表名
     * @var
     */
    private $top_join_table_name;
    /**
     * 是否分页，true为分页，false为不分页
     * @var bool
     */
    private $is_ezr = false;
    /**
     * 是否单条记录
     * @var
     */
    private $is_row;
    /**
     * 更新字段
     * @var string
     */
    private $set = '';
    /**
     * 更新字段值
     * @var array
     */
    private $set_param = array();
    /**
     * 是否启用memcache，未实现，提供接口
     * @var bool
     */
    private $is_open_mcache = false;
    private $memcache = null;

//    private $is_memcached = false;

    /**
     * limit页数
     * @var int
     */
    private $current_page = 0;
    /**
     * 最后一条sql
     * @var
     */
    private $sql = array();
    /**
     * 构造函数,单例模式
     * DB constructor.
     */
    private function __construct(){}

    /**
     * 获取DB对象
     * @return DB|null
     */
    public static function getInstance(){
        if(self::$DB==null){
            self::$DB=new self();
        }
        return self::$DB;
    }
    public static function shareHelper(){
        if(self::$DB==null){
            self::$DB=new self();
        }
        return self::$DB;
    }

    public function setConfig($dsn, $dbUser, $dbPassword, $dbCharset='utf8'){
//        self::checkDB();
        $db = PDOX::getInstance($dsn, $dbUser, $dbPassword, $dbCharset);
        $ezr = new smart_ez_results();
        $ezr->setDb($db);
        $this->db=$db;
        $this->ezr=$ezr;
    }
    
    //指定使用数据库组里面哪个数据库
    public function setDSN($index=0){
        $this->db->setDSN($index);
        return $this;
    }

//    /**
//     * 检查DB对象是否为空
//     */
//    private static function checkDB(){
//        if(self::$DB==null){
//            self::$DB=new DB();
//        }
//    }
    /**
     * 记录表名
     * @param $table_name 表名
     * @param string $alias 别名
     * @return $this
     */
    public function from($table_name,$alias=''){
//        self::checkDB();
        $this->table=$table_name;
        $this->alias=$alias;
        return $this;
    }
    /**
     * 获取表信息
     * @return array
     */
    public function getTableInfo(){
        $sql="select `column_name`,`column_comment` from information_schema.columns where
             table_name = '{$this->table}' ";
        $results=$this->db->get_results($sql);
        $rs=array();
        if($results){
            foreach ($results as $result) {
                $rs[$result->column_name]=$result->column_comment;
            }
        }
        return $rs;
    }
    /**
     * 查询条件
     * @param string $key 查询字段,若只传key则直接加入where
     * @param string $operator 操作符
     * @param string $value 查询值
     * @return $this
     */
    public function where($key='',$operator='',$value=''){
        if($key&&$operator&&strlen($value)){
            $this->where.=" AND {$key}{$operator}:{$key} ";
            $this->where_param[':'.$key]=$value;
        }
        elseif($key&&$operator&&!$value){
            $this->where.=" AND {$key}";
            $this->where_param=$operator;
        }
        elseif(trim($key)!='1'){
            $this->throwError("参数错误");
        }
        return $this;
    }

    /**
     * 限制条件
     * @param $limit
     * @return $this
     */
    public function limit($limit){
        if($limit){
            $this->limit=$limit;
        }
        return $this;
    }

    /**
     * 左联表
     * @param $table_name
     * @param string $alias
     * @return $this
     */
    public function left_join($table_name,$alias=''){
        return $this->join($table_name,$alias,"LEFT JOIN");
    }

    /**
     * 内连接
     * @param $table_name
     * @param string $alias
     * @return DB
     */
    public function inner_join($table_name,$alias=''){
        return $this->join($table_name,$alias,"INNER JOIN");
    }

    /**
     * 右连接
     * @param $table_name
     * @param string $alias
     * @return DB
     */
    public function right_join($table_name,$alias=''){
        return $this->join($table_name,$alias,"RIGHT JOIN");
    }

    /**
     * 交叉连接
     * @param $table_name
     * @param string $alias
     * @return DB
     */
    public function cross_join($table_name,$alias=''){
        return $this->join($table_name,$alias,",");
    }
    /**
     * 联表
     * @param string $join_type 联表方式
     * @param $table_name 联表表名
     * @param string $alias 联表别名
     * @return $this
     */
    public function join($table_name,$alias='',$join_type="LEFT JOIN"){
        if($table_name){
            $name = $table_name;
            if (isset($this->join[$table_name])) $name = $table_name.'_'.rand(10000,99999);
            $this->top_join_table_name=$name;
            $this->join[$this->top_join_table_name]=$table_name;
            $this->join_alias[$this->top_join_table_name]=$alias;
            $this->join_type[$this->top_join_table_name]=$join_type;
            $this->on[$this->top_join_table_name]='';
        }
        return $this;
    }

    /**
     * 可进行多次on，LEFT JOIN 的ON条件，需要添加别名
     * @param $key 字段 若只传key,则之间加到on条件内
     * @param string|$operator 操作符
     * @param string|$value 值
     * @return $this
     */
    public function on($key,$operator='',$value=''){
        if($this->top_join_table_name){
            if($key&&$operator&&strlen($value)){
                if($this->on[$this->top_join_table_name]){
                    $this->on[$this->top_join_table_name].="AND {$key}{$operator}{$value} ";
                }
                else{
                    $this->on[$this->top_join_table_name].="{$key}{$operator}{$value} ";
                }
            }
            elseif($key){
                if($this->on[$this->top_join_table_name]){
                    $this->on[$this->top_join_table_name].="AND {$key} ";
                }
                else{
                    $this->on[$this->top_join_table_name].="{$key} ";
                }
            }
        }
        return $this;
    }

    /**
     * 是否分页
     * @param boolean $is_ezr
     * @return $this
     */
    public function is_ezr($is_ezr){
        $this->is_ezr=$is_ezr;
        return $this;
    }
    public function is_page($is_ezr){
        $this->is_ezr=$is_ezr;
        return $this;
    }

    /**
     * 每页记录数
     * @param number $pagesize
     * @return $this
     */
    public function pagesize($pagesize){
        if ($pagesize>0) {
            $this->ezr->num_results_per_page=$pagesize;
        }
        return $this;
    }

    /**
     * 进行排序的字段
     * @param array $sort
     * @return $this
     */
    public function sort($sort=array()){
        $sort_str='';
        if($sort){
            foreach($sort as $k=>$v){
                if($sort_str){
                    $sort_str .= " ,{$k} {$v}";
                }
                else{
                    $sort_str= " ORDER BY {$k} {$v}";
                }
            }
        }
        $this->sort=$sort_str;
        return $this;
    }

    /**
     * 是否获取单条记录
     * @param $is_row
     * @return $this
     */
    public function is_row($is_row){
        $this->is_row=$is_row;
        return $this;
    }

    /**
     * 是否启用memcahce
     * @param $is_memcached
     * @return $this
     */
    public function is_memcached($is_memcached){
        global $memcache_link;
        $this->is_open_mcache=$is_memcached;
        if($this->memcache==null){
//            $this->memcache=new memcache();
            $this->memcache=mcache();
        }
//        $this->memcache->connect($memcache_link[0],$memcache_link[1]);
        return $this;
    }

    /**
     * 设置页数
     * @param mixed $current_page
     * @return $this
     */
    public function setCurrentPage($current_page)
    {
        $this->current_page = $current_page;
        return $this;
    }
    public function curPage($current_page)
    {
        $this->current_page = $current_page;
        return $this;
    }

    /**
     * 更新的字段
     * @param array $data
     * @return $this
     */
    public function set($data=array()){
        if(count($data)){
            foreach ($data as $key => $value) {
                //处理类如“logins=logins+1”等
                if((is_array($value)||strlen($value))&&$key!='id'){
                    if (is_array($value) && count($value)==2) {
                        $value = $value[0].$value[1];
                    }
                    if ($this->set) {
                        $this->set .= ", `{$key}`=:$key";
                    } else {
                        $this->set = "`{$key}`=:$key";
                    }
                    $this->set_param[":$key"]=$value;
                }
            }
        }
        return $this;
    }

    /**
     * 查询
     * @param string $fields 查询字段,若联表查询，请加别名
     * @param string $key 若开始memcached可以传入设置的memcached的flag
     * @return array|null
     * @throws Exception
     */
    public function find($fields='*'){//},$key=DEFAULT_MEMCACHED_SET){
        //判断是否有server
//        if($this->is_memcached&&!count(mcached($key)->getServerList())){
//            $this->writeLog("memcached","memcached配置参数错误");
//            return null;
//        }
        $obj=null;
        if(!$this->table) return ("表名未设置");
        if($fields=='*'&&$this->alias!=''){
            $field=$this->alias.".*";
        }
        elseif($fields=='*'&&$this->alias==''&&isset($this->join)&&count($this->join)){
            $field=$this->table.".*";
        }
        else{
            $field=$fields;
        }
        $left_join='';
        if(isset($this->join)&&count($this->join)>0){
            foreach($this->join as $k=>$v){
                $left_join.="{$this->join_type[$k]} {$v} {$this->join_alias[$k]} ";
                if($this->on[$k]){
                    $left_join.="ON {$this->on[$k]}";
                }
                if($fields=='*'){
                    $t = ($this->join_alias[$k]!='')?$this->join_alias[$k]:($v);
                    $field.=",".$t.".*";
                }
            }
        }
        $sql="SELECT {$field} FROM {$this->table} {$this->alias} {$left_join} WHERE {$this->where} {$this->sort}";
        $this->sql[]=$sql;
        if($this->is_open_mcache&&$this->memcache!=null){
            $s=$sql;
            if($this->is_ezr) $s.=(isset($_REQUEST['BRSR'])?$_REQUEST['BRSR']:'0');
            $data=$this->memcache->get($s);
            if($data!=null&&(time()-$data['add_time'])<OVER_TIME){
                if($this->is_ezr) $this->ezr->set_navigation($data['navigation']);
                return $data['data'];
            }
        }
        try{
            if($this->is_row||$this->limit==1){
                $obj=$this->db->get_row_bind($sql,$this->where_param);
                $this->data=$obj;
                $this->origin_data=!$obj?NULL:(clone $obj);
            }
            elseif($this->is_ezr){
                $obj=$this->ezr->get_results($sql,$this->where_param);
            }
            elseif($this->limit==0){
                $obj=$this->db->get_results_bind($sql,$this->where_param);
            }
            else{
                $l = $this->current_page*$this->limit.",".$this->limit;
                $sql.=" LIMIT {$l}";
                $obj=$this->db->get_results_bind($sql,$this->where_param);
            }
            if($this->printLog){
                $this->writeLog("debug",$sql);
                $this->writeLog("debug","参数：".json_encode($this->where_param));
            }
        }catch(Exception $e){
            $this->writeLog($e->getMessage(),$sql);
            throw $e;
        }
        if($this->is_open_mcache&&$this->memcache!=null){
            $s=$sql;
            $data=array('add_time'=>time(),'data'=>$obj);
            if($this->is_ezr){
                $data['navigation']=$this->get_navigation();
                $s.=(isset($_REQUEST['BRSR'])?$_REQUEST['BRSR']:'0');
            }
            $da=$this->memcache->get($s);
            if($da!=null){
                $this->memcache->replace($s,$data);
            }
            else{
                $this->memcache->set($s,$data);
            }
        }
        $this->clear();
        return $obj;
    }

    /**
     * 更新操作
     * 如果进行了查询操作可以直接设置表名后直接update
     * @return bool|int|PDOStatement|void
     * @throws Exception
     */
    public function update(){
        if(!$this->table) return ("表名未设置");
        if($this->set){
            $sql="UPDATE {$this->table} SET {$this->set} WHERE {$this->where}";
        }
        elseif($this->data){
            $row=get_object_vars($this->origin_data);
            $set = '';
            foreach ($this->data as $key => $value) {
                if($key!='id'&&$row[$key]!=$value){
                    if ($set) {
                        $set .= ", `{$key}`=:$key";
                    } else {
                        $set = "`{$key}`=:$key";
                    }
                    $this->set_param[":$key"]=$value;
                }
            }
            $sql = "UPDATE {$this->table} SET {$set} WHERE id='{$this->data->id}'";
        }
        else{
            $this->clear();
            return true;
        }
        $this->sql[]=$sql;
        try{
            if($this->printLog){
                $this->writeLog("debug",$sql);
                $this->writeLog("debug","参数：".json_encode($this->set_param));
            }
            $result= $this->db->query_bind($sql,array_merge($this->set_param,$this->where_param),'all',false,"update");
            $this->clear();
            return $result;
        }catch(Exception $e){
            $this->writeLog($e->getMessage(),$sql);
            throw $e;
        }
    }

    /**
     * 插入操作
     * @param $data
     * @return int|PDOStatement|void
     * @throws Exception
     */
    public function insert($data){
        //判断表名是否存在
        if(!$this->table) return("model表名未设置");
        $fields = '';
        $values = '';
        $v=array();
        if (is_array($data)) {
            foreach ($data as $key => $value) {
                if ($fields) {
                    $fields .= ", `{$key}`";
                    $values .= ",:".$key;
                    $v[":".$key]=$value;
                } else {
                    $fields = "`{$key}`";
                    $values = ":".$key;
                    $v[":".$key]=$value;
                }
            }
        }
        $sql = "INSERT INTO {$this->table} ({$fields}) VALUES ({$values})";
        $this->sql[]=$sql;
        $id=0;
        try {
            if($this->printLog){
                $this->writeLog("debug",$sql);
            }
            return $this->db->query_bind($sql,$v,'all',false,"insert");
        }catch(Exception $e){
            $this->writeLog($e->getMessage().$e->getFile().$e->getLine(),$sql);
            throw $e;
        }
        return $id;
    }

    /**
     * 获取插入id
     * @return mixed
     */
    public function get_insert_id(){
        return $this->db->get_insert_id();
    }
    public function insert_id(){
        return $this->db->get_insert_id();
    }

    /**
     * 删除操作
     * @return int|PDOStatement|void
     * @throws Exception
     */
    public function delete(){
        if(!$this->table) return("表名未设置");
        $sql="DELETE FROM {$this->table} WHERE {$this->where}";
        $this->sql[]=$sql;
        try {
            if($this->printLog){
                $this->writeLog("debug",$sql);
            }
            $result= $this->db->query_bind($sql,$this->where_param,'All', false, "update");
            $this->clear();
            return $result;
        }catch(Exception $e){
            $this->writeLog($e->getMessage(),$sql);
            throw $e;
        }
    }

    /**
     * 计算条数
     * @return null
     * @throws Exception
     */
    public function count(){
        if(!$this->table) return("表名未设置");
        $sql = "SELECT count(*) FROM {$this->table} WHERE {$this->where}";
        $this->sql[]=$sql;
        try{
            if($this->printLog){
                $this->writeLog("debug",$sql);
            }
            $result= $this->db->get_var_bind($sql,$this->where_param);
            $this->clear();
            return $result;
        }catch(Exception $e){
            $this->writeLog($e->getMessage(),$sql);
            throw $e;
        }
    }

    /**
     * 是否存在
     * @return 0为不存在，大于0为存在
     */
    public function exists(){
        return $this->count();
    }

    /**
     * 计算总数
     * @param $field
     * @return null|string
     * @throws Exception
     */
    public function sum($field){
        if(!$field) return "请输入字段名";
        $sql="SELECT SUM({$field}) FROM {$this->table} WHERE {$this->where}";
        $this->sql[]=$sql;
        try{
            if($this->printLog){
                $this->writeLog("debug",$sql);
            }
            $sum = $this->db->get_var_bind($sql,$this->where_param);
            $this->clear();
            return $sum?$sum:0;
        }catch(Exception $e){
            $this->writeLog($e->getMessage(),$sql);
            throw $e;
        }
    }
    /**
     * union 联表查询
     * @param array $table_names
     * @param string $fields
     * @return null
     * @throws Exception
     */
    public function union($table_names=array(),$where,$where_param,$fields='*'){
        if(count($table_names)||$fields=='') return("参数不正确");
        $obj=null;
        $union='';
        foreach ($table_names as $table_name) {
            if($union){
                $union.="UNION(SELECT {$fields} FROM {$table_name} WHERE {$where})";
            }
            else{
                $union.="(SELECT {$fields} FROM {$table_name} WHERE {$where})";
            }
        }
        $limit='';
        if($this->limit>0){
            $limit.="LIMIT {$this->limit}";
        }
        $sql="SELECT * FROM ({$union}) {$this->sort} {$limit}";
        $this->sql[]=$sql;
        try{
            if($this->is_ezr){
                $obj=$this->ezr->get_results($sql,$where_param);
            }
            else{
                $obj=$this->db->get_results_bind($sql,$where_param);
            }
            if($this->printLog){
                $this->writeLog("debug",$sql);
            }
            $this->clear();
        }catch(Exception $e){
            $this->writeLog($e->getMessage(),$sql);
            throw $e;
        }
        return $obj;
    }

    /**
     * 进行行锁，更新数据
     * @param $id 必须要有id
     * @return array|null 返回行记录
     */
    public function for_update($id){
        if($id==null) return("请输入id");
        $sql="SELECT * FROM {$this->table} WHERE id=:id FOR UPDATE ";
        $this->sql[]=$sql;
        return $this->db->get_row_bind($sql,array(":id"=>$id));
    }

    /**
     * 获取单值,使用自定义函数的情况
     * @param $fields
     * @return null
     */
    public function get_var($fields){
        $sql="SELECT {$fields} FROM {$this->table} WHERE {$this->where} {$this->sort}";
        $this->sql[]=$sql;
        $vals = $this->db->get_var_bind($sql,$this->where_param);
        $this->clear();
        return $vals;
    }

    /**
     * 清空缓存
     */
    public function clear(){
        //请空数据
        $this->where='1 ';
        $this->sort='';
        $this->table='';
        $this->alias='';
        $this->set='';
        $this->is_row=false;
        $this->is_ezr=false;
        $this->join=array();
        $this->on=array();
        $this->join_alias=array();
        $this->join_type=array();
        $this->set_param=array();
        $this->where_param=array();
    }

    /**
     * @return boolean
     */
    public function isPrintLog()
    {
        return $this->printLog;
    }

    /**
     * @param boolean $printLog
     */
    public function setPrintLog($printLog)
    {
//        self::checkDB();
        $this->printLog=$printLog;
    }

    public function throwError($strMsg){
        $this->db->outputError($strMsg);
    }

    /**
     * 打印sql
     * @param $sql
     */
    public function writeLog($title, $content){
        global $app, $act;
        $member_id = (isset($_SESSION['member']) && is_object($_SESSION['member'])) ? $_SESSION['member']->id : 0;
		$msg = array('app'=>$app, 'act'=>$act, 'ip'=>ip(), 'member_id'=>$member_id, 'sql'=>$content);
		write_log($title.':'.json_encode($msg), '/temp/sql_log-'.date('Ymd').'.txt');
    }

    ///////////////////////////////////////////////////华丽的分割线//////////////////////////////////////
    //事务处理
    public function transaction($function){
//        self::checkDB();
        $this->db->beginTransaction();
        try{
            $function();
            $this->db->commitTransaction();
        }catch(Exception $e){
            $this->db->rollBack();
        }
    }

    /**
     * 开启事务；
     */
    public function beginTransaction(){
//        self::checkDB();
        $this->db->beginTransaction();
    }

    /**
     * @return bool
     */
    public function inTransaction(){
        return $this->db->inTransaction();
    }

    /**
     * 回滚事务
     */
    public function rollBack(){
//        self::checkDB();
        $this->db->rollBack();
    }

    /**
     * 事务结束
     */
    public function commit(){
//        self::checkDB();
        $this->db->commitTransaction();
    }

    /**
     * 设置页数
     * @param $page
     */
    public function set_num_results_per_page($page){
//        self::checkDB();
        $this->ezr->num_results_per_page=$page;
    }

    /**
     * 设置分页的get参数
     * @param $key
     * @param $value
     */
    public function set_qs_val($key,$value){
//        self::checkDB();
        $this->ezr->set_qs_val($key,$value);
    }

    /**
     * 获取底部分页对象
     * @return mixed
     */
    public function get_navigation(){
//        self::checkDB();
        return $this->ezr->get_navigation();
    }

    /**
     * @return mixed
     */
    public function getSql()
    {
        return $this->sql;
    }

}

class DBB {
    public $insert_id;
    
    public function query($sql) {
        DB::getInstance()->db->pdo->query($sql);
        $this->insert_id = DB::getInstance()->db->pdo->lastInsertId();
        return $this->insert_id;
    }
    
    public function get_results($sql) {
        return DB::getInstance()->db->get_results($sql);
    }
    
    public function get_row($sql) {
        return DB::getInstance()->db->get_row($sql);
    }
    
    public function get_var($sql){
        return DB::getInstance()->db->get_var($sql);
    }
}